#!/usr/bin/env python
# -*- coding: UTF-8 -*-
"""
@Project ：back 
@File    ：tools.py
@IDE     ：PyCharm 
@Author  ：七分是你
@Date    ：2024/6/28 下午8:32 
@explain : 
"""
from sqlalchemy import and_, or_


def build_query(query, model, **kwargs):
    filters = []
    or_filters = []
    for column, value in kwargs.items():
        if not hasattr(model, column):
            continue
        column_attr = getattr(model, column)
        if isinstance(value, tuple) and len(value) == 2:  # value = (1,2)
            # 范围查询
            filters.append(column_attr.between(*value))
        elif isinstance(value, list):  # value = [1,2]  （例如：!=, <, <=, >, >=） ['ne','lt','le','gt','ge'] ['ne',1]不等于1
            if len(value) == 2 and value[0] in ['ne', 'lt', 'le', 'gt', 'ge']:
                op, val = value
                if op == 'ne':
                    filters.append(column_attr != val)
                elif op == 'lt':
                    filters.append(column_attr < val)
                elif op == 'le':
                    filters.append(column_attr <= val)
                elif op == 'gt':
                    filters.append(column_attr > val)
                elif op == 'ge':
                    filters.append(column_attr >= val)
                else:
                    # in查询
                    filters.append(column_attr.in_(value))
        elif isinstance(value, str) and '%' in value:
            # 模糊查询
            filters.append(column_attr.like(value))
        else:
            # 等值查询
            filters.append(column_attr == value)
    if filters:
        query = query.filter(and_(*filters))
    if 'whereOr' in kwargs:  # {"whereOr":{"字段名":字段值,"name":"zs"}}
        or_conditions = []
        if isinstance(kwargs['whereOr'], dict):
            for column, value in kwargs['whereOr'].items():
                if not hasattr(model, column):
                    continue
                column_attr = getattr(model, column)
                if isinstance(value, tuple) and len(value) == 2:
                    or_filters.append(column_attr.between(*value))
                elif isinstance(value, list):
                    if len(value) == 2 and value[0] in ['ne', 'lt', 'le', 'gt', 'ge']:
                        op, val = value
                        if op == 'ne':
                            or_filters.append(column_attr != val)
                        elif op == 'lt':
                            or_filters.append(column_attr < val)
                        elif op == 'le':
                            or_filters.append(column_attr <= val)
                        elif op == 'gt':
                            or_filters.append(column_attr > val)
                        elif op == 'ge':
                            or_filters.append(column_attr >= val)
                        else:
                            or_filters.append(column_attr.in_(value))
                elif isinstance(value, str) and '%' in value:
                    or_filters.append(column_attr.like(value))
                else:
                    or_filters.append(column_attr == value)
    if or_filters:
        query = query.filter(or_(*or_filters))
    return query
